package dao.mysql;

import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;

import model.LeaveDTO;
import dao.DAOException;
import dao.ILeaveDAO;

public class LeaveDAO implements  ILeaveDAO{
	Connection conn;

	public LeaveDAO() {
		MyConnection myconn = new MyConnection();
		conn = myconn.getConnection();
	}
	
	@Override
	public LeaveDTO findLeave(String id) throws DAOException {
		String selectSql = "SELECT * FROM leave WHERE leaveId='"+id.trim()+"';";
		LeaveDTO leaveDTO = new LeaveDTO();
		
		try
		{
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,"Executing Select:"+ selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while(rs.next())
			{
				leaveDTO.setLeaveId(rs.getString("leaveId"));
				leaveDTO.setLeaveType(rs.getString("leaveType"));
				leaveDTO.setAllowTotalDays(rs.getString("allowTotalDays"));
			}
			st.close();
			
		}catch(SQLException sqlex)
		{
			String error = "Error Selecting leave.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try
			{
				conn.close();
			}catch(Exception e){}
			
		}
		
		return leaveDTO;
	}
	
	@Override
	public ArrayList<LeaveDTO> findAllLeave() throws DAOException {
		ArrayList<LeaveDTO> items = new ArrayList<LeaveDTO>();
		String selectSql = "SELECT * FROM leave";
		try
		{
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			
			while(rs.next())
			{
				LeaveDTO leaveDTO = new LeaveDTO();
				leaveDTO.setLeaveId(rs.getString("leaveId"));
				leaveDTO.setLeaveType(rs.getString("leaveType"));
				leaveDTO.setAllowTotalDays(rs.getString("allowTotalDays"));
			}
			st.close();
		}catch(SQLException sqlex)
		{
			String error = "Error Selecting leave.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try
			{
				conn.close();
			}
			catch(Exception ex){};
			
		}
		
		return null;
	}
	@Override
	public void insertLeave(LeaveDTO leave) throws DAOException {
		String insertSql = "INSERT INTO leave(leaveId,leaveType,allowTotalDays)" +
				" VALUES(?,?,?);";
		try
		{
			PreparedStatement pst = conn.prepareStatement(insertSql);
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			
			pst.setString(1, leave.getLeaveId());
			pst.setString(2, leave.getLeaveType());
			pst.setString(3, leave.getAllowTotalDays());
						
			if(pst.executeUpdate() != 1)
			{
				String error = "Error Inserting into Leave.";
				Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
				throw new DAOException();
			}
			
			pst.close();
		}catch(SQLException sqlex)
		{
			String error = "Error Inserting into Leave.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}	
	}
	@Override
	public void updateLeave(LeaveDTO leave) throws DAOException {
		String updateSql = "UPDATE leave SET leaveType=?,allowTotalDays=? WHERE leaveId=?";
		try
		{
			PreparedStatement pst = conn.prepareStatement(updateSql);
			pst.setString(1, leave.getLeaveType());
			pst.setString(2, leave.getAllowTotalDays());
			if(pst.executeUpdate() != 1)
			{
				String error = "Error updating Leave.";
				Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
				throw new DAOException();
			}
			
			pst.close();
		}catch(SQLException sqlex)
		{
			String error = "Error Updating Leave.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	@Override
	public void deleteLeave(LeaveDTO leave) throws DAOException {
		String updateSql = "DELETE FROM leave WHERE leaveId=?";
		try
		{
			PreparedStatement pst = conn.prepareStatement(updateSql);
			pst.setString(1, leave.getLeaveId());
			
			if(pst.executeUpdate() != 1)
			{
				String error = "Error Deleting Role.";
				Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
				throw new DAOException();
			}
			
			pst.close();
		}catch(SQLException sqlex)
		{
			String error = "Error Deleting Leave.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

}
